import os
import time
import subprocess
from datetime import datetime, timedelta
from django.utils import timezone
from django.shortcuts import render
from django.db import connections, connection
from django.conf import settings
from rest_framework import status, viewsets
from rest_framework.decorators import api_view, permission_classes, action
from rest_framework.permissions import IsAuthenticated
from rest_framework.response import Response
from rest_framework.pagination import PageNumberPagination
from common.response import APIResponse
from .models import (
    DatabaseConnection, DatabaseBackup, DatabaseMonitoring,
    QueryHistory, DatabaseAlert, SQLAuditRecord
)
from .serializers import (
    DatabaseConnectionSerializer, DatabaseConnectionCreateSerializer,
    DatabaseBackupSerializer, DatabaseMonitoringSerializer,
    QueryHistorySerializer, DatabaseAlertSerializer,
    SQLQuerySerializer, DatabaseStatsSerializer
)
from .managers import DatabaseConnectionManager
from .backup_manager import DatabaseBackupManager
from .monitoring_manager import DatabaseMonitoringManager
from .inception_manager import InceptionManager, get_mysql_version, test_connection_compatibility


class DatabaseConnectionViewSet(viewsets.ModelViewSet):
    """数据库连接管理"""
    queryset = DatabaseConnection.objects.all()
    permission_classes = [IsAuthenticated]

    def get_serializer_class(self):
        if self.action == 'create':
            return DatabaseConnectionCreateSerializer
        return DatabaseConnectionSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        name = self.request.GET.get('name', '').strip()
        engine = self.request.GET.get('engine', '').strip()

        if name:
            queryset = queryset.filter(name__icontains=name)
        if engine:
            queryset = queryset.filter(engine=engine)

        return queryset

    @action(detail=True, methods=['post'])
    def test_connection(self, request, pk=None):
        """测试数据库连接"""
        db_conn = self.get_object()

        result = DatabaseConnectionManager.test_connection(db_conn)

        if result['success']:
            return Response({
                'success': True,
                'message': result['message'],
                'response_time': result['response_time']
            })
        else:
            return Response({
                'success': False,
                'message': result['message'],
                'response_time': result['response_time']
            }, status=status.HTTP_400_BAD_REQUEST)

    @action(detail=True, methods=['get'])
    def get_tables(self, request, pk=None):
        """获取数据库表列表"""
        db_conn = self.get_object()

        try:
            tables = DatabaseConnectionManager.get_table_list(db_conn)

            return Response({
                'success': True,
                'data': tables
            })

        except Exception as e:
            return Response({
                'success': False,
                'message': str(e)
            }, status=status.HTTP_400_BAD_REQUEST)

    @action(detail=True, methods=['get'])
    def get_info(self, request, pk=None):
        """获取数据库信息"""
        db_conn = self.get_object()

        try:
            info = DatabaseConnectionManager.get_database_info(db_conn)

            return Response({
                'success': True,
                'data': info
            })

        except Exception as e:
            return Response({
                'success': False,
                'message': str(e)
            }, status=status.HTTP_400_BAD_REQUEST)

    @action(detail=True, methods=['get'])
    def check_compatibility(self, request, pk=None):
        """检查数据库兼容性"""
        db_conn = self.get_object()

        try:
            result = test_connection_compatibility(db_conn)

            if result['success']:
                # 更新数据库版本信息
                if db_conn.engine == 'mysql' and 'version' in result:
                    db_conn.mysql_version = result['version'].split('-')[0]
                    db_conn.save()

                return Response({
                    'success': True,
                    'data': result
                })
            else:
                return Response({
                    'success': False,
                    'message': result.get('error', '兼容性检查失败')
                }, status=status.HTTP_400_BAD_REQUEST)

        except Exception as e:
            return Response({
                'success': False,
                'message': str(e)
            }, status=status.HTTP_400_BAD_REQUEST)


class DatabaseBackupViewSet(viewsets.ModelViewSet):
    """数据库备份管理"""
    queryset = DatabaseBackup.objects.all()
    serializer_class = DatabaseBackupSerializer
    permission_classes = [IsAuthenticated]

    def get_queryset(self):
        queryset = super().get_queryset()
        connection_id = self.request.GET.get('connection_id')
        status_filter = self.request.GET.get('status')

        if connection_id:
            queryset = queryset.filter(connection_id=connection_id)
        if status_filter:
            queryset = queryset.filter(status=status_filter)

        return queryset

    @action(detail=False, methods=['post'])
    def create_backup(self, request):
        """创建数据库备份"""
        connection_id = request.data.get('connection_id')
        backup_name = request.data.get('backup_name')
        backup_type = request.data.get('backup_type', 'full')

        if not connection_id:
            return Response({
                'success': False,
                'message': '缺少数据库连接ID'
            }, status=status.HTTP_400_BAD_REQUEST)

        try:
            db_conn = DatabaseConnection.objects.get(id=connection_id)
        except DatabaseConnection.DoesNotExist:
            return Response({
                'success': False,
                'message': '数据库连接不存在'
            }, status=status.HTTP_404_NOT_FOUND)

        backup_manager = DatabaseBackupManager()
        result = backup_manager.create_backup(
            db_conn=db_conn,
            backup_name=backup_name,
            backup_type=backup_type,
            user=request.user
        )

        if result['success']:
            return Response({
                'success': True,
                'data': {
                    'backup_id': result['backup_id'],
                    'file_size': result['file_size']
                },
                'message': result['message']
            })
        else:
            return Response({
                'success': False,
                'message': result['message']
            }, status=status.HTTP_400_BAD_REQUEST)

    @action(detail=True, methods=['post'])
    def restore_backup(self, request, pk=None):
        """恢复数据库备份"""
        backup = self.get_object()
        target_connection_id = request.data.get('target_connection_id')

        target_db_conn = None
        if target_connection_id:
            try:
                target_db_conn = DatabaseConnection.objects.get(id=target_connection_id)
            except DatabaseConnection.DoesNotExist:
                return Response({
                    'success': False,
                    'message': '目标数据库连接不存在'
                }, status=status.HTTP_404_NOT_FOUND)

        backup_manager = DatabaseBackupManager()
        result = backup_manager.restore_backup(backup.id, target_db_conn)

        if result['success']:
            return Response({
                'success': True,
                'message': result['message']
            })
        else:
            return Response({
                'success': False,
                'message': result['message']
            }, status=status.HTTP_400_BAD_REQUEST)

    @action(detail=True, methods=['delete'])
    def delete_backup(self, request, pk=None):
        """删除备份"""
        backup = self.get_object()

        backup_manager = DatabaseBackupManager()
        result = backup_manager.delete_backup(backup.id)

        if result['success']:
            return Response({
                'success': True,
                'message': result['message']
            })
        else:
            return Response({
                'success': False,
                'message': result['message']
            }, status=status.HTTP_400_BAD_REQUEST)


@api_view(['POST'])
@permission_classes([IsAuthenticated])
def collect_monitoring_metrics(request):
    """收集数据库监控指标"""
    connection_id = request.data.get('connection_id')

    if not connection_id:
        return Response({
            'success': False,
            'message': '缺少数据库连接ID'
        }, status=status.HTTP_400_BAD_REQUEST)

    try:
        db_conn = DatabaseConnection.objects.get(id=connection_id)
    except DatabaseConnection.DoesNotExist:
        return Response({
            'success': False,
            'message': '数据库连接不存在'
        }, status=status.HTTP_404_NOT_FOUND)

    monitoring_manager = DatabaseMonitoringManager()
    result = monitoring_manager.collect_metrics(db_conn)

    if result['success']:
        return Response({
            'success': True,
            'data': result['metrics'],
            'message': '监控指标收集成功'
        })
    else:
        return Response({
            'success': False,
            'message': result['message']
        }, status=status.HTTP_400_BAD_REQUEST)


@api_view(['GET'])
@permission_classes([IsAuthenticated])
def get_monitoring_history(request):
    """获取监控历史数据"""
    connection_id = request.GET.get('connection_id')
    hours = int(request.GET.get('hours', 24))

    if not connection_id:
        return Response({
            'success': False,
            'message': '缺少数据库连接ID'
        }, status=status.HTTP_400_BAD_REQUEST)

    try:
        db_conn = DatabaseConnection.objects.get(id=connection_id)
    except DatabaseConnection.DoesNotExist:
        return Response({
            'success': False,
            'message': '数据库连接不存在'
        }, status=status.HTTP_404_NOT_FOUND)

    monitoring_manager = DatabaseMonitoringManager()
    history = monitoring_manager.get_monitoring_history(db_conn, hours)

    serializer = DatabaseMonitoringSerializer(history, many=True)

    return Response({
        'success': True,
        'data': serializer.data
    })


@api_view(['GET'])
@permission_classes([IsAuthenticated])
def get_performance_summary(request):
    """获取性能摘要"""
    connection_id = request.GET.get('connection_id')
    hours = int(request.GET.get('hours', 24))

    if not connection_id:
        return Response({
            'success': False,
            'message': '缺少数据库连接ID'
        }, status=status.HTTP_400_BAD_REQUEST)

    try:
        db_conn = DatabaseConnection.objects.get(id=connection_id)
    except DatabaseConnection.DoesNotExist:
        return Response({
            'success': False,
            'message': '数据库连接不存在'
        }, status=status.HTTP_404_NOT_FOUND)

    monitoring_manager = DatabaseMonitoringManager()
    summary = monitoring_manager.get_performance_summary(db_conn, hours)

    if summary:
        return Response({
            'success': True,
            'data': summary
        })
    else:
        return Response({
            'success': False,
            'message': '暂无监控数据'
        }, status=status.HTTP_404_NOT_FOUND)


class QueryHistoryViewSet(viewsets.ReadOnlyModelViewSet):
    """SQL查询历史管理"""
    queryset = QueryHistory.objects.all()
    serializer_class = QueryHistorySerializer
    permission_classes = [IsAuthenticated]

    def get_queryset(self):
        queryset = super().get_queryset()
        connection_id = self.request.GET.get('connection_id')
        query_type = self.request.GET.get('query_type')
        status_filter = self.request.GET.get('status')

        if connection_id:
            queryset = queryset.filter(connection_id=connection_id)
        if query_type:
            queryset = queryset.filter(query_type=query_type)
        if status_filter:
            queryset = queryset.filter(status=status_filter)

        return queryset.order_by('-executed_at')


class DatabaseAlertViewSet(viewsets.ModelViewSet):
    """数据库告警管理"""
    queryset = DatabaseAlert.objects.all()
    serializer_class = DatabaseAlertSerializer
    permission_classes = [IsAuthenticated]

    def get_queryset(self):
        queryset = super().get_queryset()
        connection_id = self.request.GET.get('connection_id')
        alert_type = self.request.GET.get('alert_type')
        severity = self.request.GET.get('severity')
        is_resolved = self.request.GET.get('is_resolved')

        if connection_id:
            queryset = queryset.filter(connection_id=connection_id)
        if alert_type:
            queryset = queryset.filter(alert_type=alert_type)
        if severity:
            queryset = queryset.filter(severity=severity)
        if is_resolved is not None:
            queryset = queryset.filter(is_resolved=is_resolved.lower() == 'true')

        return queryset.order_by('-created_at')

    @action(detail=True, methods=['post'])
    def resolve_alert(self, request, pk=None):
        """解决告警"""
        alert = self.get_object()

        alert.is_resolved = True
        alert.resolved_at = timezone.now()
        alert.resolved_by = request.user
        alert.save()

        return Response({
            'success': True,
            'message': '告警已解决'
        })

    @action(detail=False, methods=['post'])
    def resolve_all(self, request):
        """解决所有告警"""
        connection_id = request.data.get('connection_id')

        queryset = DatabaseAlert.objects.filter(is_resolved=False)
        if connection_id:
            queryset = queryset.filter(connection_id=connection_id)

        count = queryset.update(
            is_resolved=True,
            resolved_at=timezone.now(),
            resolved_by=request.user
        )

        return Response({
            'success': True,
            'message': f'已解决 {count} 个告警'
        })


@api_view(['POST'])
@permission_classes([IsAuthenticated])
def execute_sql_query(request):
    """执行SQL查询"""
    serializer = SQLQuerySerializer(data=request.data)
    if not serializer.is_valid():
        return Response({
            'success': False,
            'message': '参数验证失败',
            'errors': serializer.errors
        }, status=status.HTTP_400_BAD_REQUEST)

    connection_id = serializer.validated_data['connection_id']
    query = serializer.validated_data['query']

    try:
        db_conn = DatabaseConnection.objects.get(id=connection_id)
    except DatabaseConnection.DoesNotExist:
        return Response({
            'success': False,
            'message': '数据库连接不存在'
        }, status=status.HTTP_404_NOT_FOUND)

    # 使用连接管理器执行查询
    result = DatabaseConnectionManager.execute_query(db_conn, query)

    # 记录查询历史
    query_type = query.strip().split()[0].upper()

    if result['success']:
        QueryHistory.objects.create(
            connection=db_conn,
            query=query,
            query_type=query_type if query_type in ['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'CREATE', 'ALTER', 'DROP'] else 'OTHER',
            execution_time=result['execution_time'],
            rows_affected=result['data'].get('row_count', result['data'].get('rows_affected', 0)),
            status='success',
            executed_by=request.user
        )

        return Response({
            'success': True,
            'data': result['data'],
            'execution_time': result['execution_time']
        })
    else:
        QueryHistory.objects.create(
            connection=db_conn,
            query=query,
            query_type=query_type if query_type in ['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'CREATE', 'ALTER', 'DROP'] else 'OTHER',
            execution_time=result['execution_time'],
            rows_affected=0,
            status='error',
            error_message=result['error'],
            executed_by=request.user
        )

        return Response({
            'success': False,
            'message': f'SQL执行失败: {result["error"]}',
            'execution_time': result['execution_time']
        }, status=status.HTTP_400_BAD_REQUEST)


@api_view(['GET'])
@permission_classes([IsAuthenticated])
def database_stats(request):
    """获取数据库统计信息"""
    try:
        # 统计数据
        total_connections = DatabaseConnection.objects.count()
        active_connections = DatabaseConnection.objects.filter(is_active=True).count()

        total_backups = DatabaseBackup.objects.count()
        recent_backups = DatabaseBackup.objects.filter(
            created_at__gte=datetime.now() - timedelta(days=7)
        ).count()

        total_queries = QueryHistory.objects.count()
        recent_queries = QueryHistory.objects.filter(
            executed_at__gte=datetime.now() - timedelta(days=1)
        ).count()

        active_alerts = DatabaseAlert.objects.filter(is_resolved=False).count()
        resolved_alerts = DatabaseAlert.objects.filter(is_resolved=True).count()

        stats_data = {
            'total_connections': total_connections,
            'active_connections': active_connections,
            'total_backups': total_backups,
            'recent_backups': recent_backups,
            'total_queries': total_queries,
            'recent_queries': recent_queries,
            'active_alerts': active_alerts,
            'resolved_alerts': resolved_alerts
        }

        serializer = DatabaseStatsSerializer(stats_data)

        return Response({
            'success': True,
            'data': serializer.data
        })

    except Exception as e:
        return Response({
            'success': False,
            'message': f'获取统计信息失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['POST'])
@permission_classes([IsAuthenticated])
def audit_sql(request):
    """SQL审核"""
    try:
        connection_id = request.data.get('connection_id')
        sql_content = request.data.get('sql_content', '').strip()

        if not connection_id or not sql_content:
            return Response({
                'success': False,
                'message': '连接ID和SQL内容不能为空'
            }, status=status.HTTP_400_BAD_REQUEST)

        try:
            db_conn = DatabaseConnection.objects.get(id=connection_id)
        except DatabaseConnection.DoesNotExist:
            return Response({
                'success': False,
                'message': '数据库连接不存在'
            }, status=status.HTTP_404_NOT_FOUND)

        # 创建Inception管理器并审核SQL
        inception_manager = InceptionManager(db_conn)
        result = inception_manager.audit_sql(sql_content, request.user)

        if result['success']:
            return Response({
                'success': True,
                'data': {
                    'audit_record_id': result['audit_record'].id,
                    'results': result['results'],
                    'error_level': result['error_level'],
                    'warning_count': result['warning_count'],
                    'error_count': result['error_count'],
                    'can_execute': result['can_execute']
                }
            })
        else:
            return Response({
                'success': False,
                'message': result['error']
            }, status=status.HTTP_400_BAD_REQUEST)

    except Exception as e:
        return Response({
            'success': False,
            'message': f'SQL审核失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['POST'])
@permission_classes([IsAuthenticated])
def execute_sql(request):
    """执行已审核的SQL"""
    try:
        audit_record_id = request.data.get('audit_record_id')

        if not audit_record_id:
            return Response({
                'success': False,
                'message': '审核记录ID不能为空'
            }, status=status.HTTP_400_BAD_REQUEST)

        try:
            audit_record = SQLAuditRecord.objects.get(id=audit_record_id)
        except SQLAuditRecord.DoesNotExist:
            return Response({
                'success': False,
                'message': '审核记录不存在'
            }, status=status.HTTP_404_NOT_FOUND)

        # 创建Inception管理器并执行SQL
        inception_manager = InceptionManager(audit_record.connection)
        result = inception_manager.execute_sql(audit_record, request.user)

        if result['success']:
            return Response({
                'success': True,
                'data': {
                    'affected_rows': result['affected_rows'],
                    'execution_time': result['execution_time']
                }
            })
        else:
            return Response({
                'success': False,
                'message': result['error']
            }, status=status.HTTP_400_BAD_REQUEST)

    except Exception as e:
        return Response({
            'success': False,
            'message': f'SQL执行失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['GET'])
@permission_classes([IsAuthenticated])
def audit_records(request):
    """获取SQL审核记录"""
    try:
        connection_id = request.GET.get('connection_id')
        status_filter = request.GET.get('status')

        queryset = SQLAuditRecord.objects.all()

        if connection_id:
            queryset = queryset.filter(connection_id=connection_id)
        if status_filter:
            queryset = queryset.filter(status=status_filter)

        # 分页
        paginator = PageNumberPagination()
        paginator.page_size = 20
        page = paginator.paginate_queryset(queryset.order_by('-created_at'), request)

        records = []
        for record in page:
            records.append({
                'id': record.id,
                'connection_name': record.connection.name,
                'sql_content': record.sql_content[:200] + '...' if len(record.sql_content) > 200 else record.sql_content,
                'status': record.status,
                'error_level': record.error_level,
                'warning_count': record.warning_count,
                'error_count': record.error_count,
                'submitted_by': record.submitted_by.username,
                'created_at': record.created_at,
                'executed_at': record.executed_at,
                'affected_rows': record.affected_rows
            })

        return Response({
            'success': True,
            'data': records,
            'count': paginator.page.paginator.count,
            'next': paginator.get_next_link(),
            'previous': paginator.get_previous_link()
        })

    except Exception as e:
        return Response({
            'success': False,
            'message': f'获取审核记录失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)
